﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Zzva.Common;


namespace Zzva.ESB
{
    public class TSysMrpZzva
    {

        private static TSysMrpZzva mvarSysMrpZzva;
        private SqlConnection mvarConnect;
        private SqlTransaction mvarTransaction;


        # region Software переопределяем в связи с добавлением ссылки на SoftType 

        public bool GetSoftware(int Id, out string Obozn, out string Naim,
                                 out int Holder, out int Developer,
                                 out int SoftType)
        {
            try
            {
                SqlCommand sql;
                DataTable rst;
                SqlDataAdapter adp;
                DataRow row;
                bool Result;
                string lObozn;
                string lNaim;
                int lHolder;
                int lDeveloper;
                int lSoftType;


                sql = new SqlCommand();
                sql.Connection = mvarConnect;

                sql.CommandText = " SELECT DirSoftware.*"
                                + " FROM DirSoftware"
                                + " WHERE Id = " + Id;

                adp = new SqlDataAdapter();
                adp.SelectCommand = sql;
                rst = new DataTable();
                adp.Fill(rst);

                if (rst.Rows.Count == 1)
                {
                    row = rst.Rows[0];

                    lObozn = (string)row["Obozn"];

                    if (Convert.IsDBNull(row["Naim"]) == true) { lNaim = ""; }
                    else { lNaim = (string)row["Naim"]; }

                    if (Convert.IsDBNull(row["Holder"]) == true) { lHolder = 0; }
                    else { lHolder = (int)row["Holder"]; }

                    if (Convert.IsDBNull(row["Developer"]) == true) { lDeveloper = 0; }
                    else { lDeveloper = (int)row["Developer"]; }

                    if (Convert.IsDBNull(row["SoftType"]) == true) { lSoftType = 0; }
                    else { lSoftType = (int)row["SoftType"]; }


                    Result = true;
                }
                else
                {
                    lObozn = "";
                    lNaim = "";
                    lHolder = 0;
                    lDeveloper = 0;
                    lSoftType = 0;


                    Result = false;
                }


                Obozn = lObozn;
                Naim = lNaim;
                Holder = lHolder;
                Developer = lDeveloper;
                SoftType = lSoftType;

                return Result;
            }

            catch (Exception e) { throw e; }
            finally { }
        }

        public void EditSoftware(int pId, string pObozn, string pNaim, int pDeveloper, int pHolder,
                                 int pSoftType)
        {
            try
            {
                SqlCommand sql;
                int ColRowExecute;

                sql = new SqlCommand();
                sql.Connection = mvarConnect;


                //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                //На время первоночального заполнения справочника
                ////                sql.CommandText = " UPDATE DirSoftware"
                ////                                + " SET  Naim = @Naim, Holder = @Holder, Developer = @Developer, SoftType = @SoftType"
                ////                                + " WHERE Id = " + pId;
                sql.CommandText = " UPDATE DirSoftware"
                                + " SET  Naim = @Naim, Holder = @Holder, Developer = @Developer, Obozn = @Obozn, SoftType = @SoftType"
                                + " WHERE Id = " + pId;
                sql.Parameters.Add("@Obozn", SqlDbType.NVarChar);
                sql.Parameters["@Obozn"].Value = pObozn;



                sql.Parameters.Add("@Naim", SqlDbType.NVarChar);
                sql.Parameters.Add("@Developer", SqlDbType.Int);
                sql.Parameters.Add("@Holder", SqlDbType.Int);
                sql.Parameters.Add("@SoftType", SqlDbType.Int);


                sql.Parameters["@Naim"].Value = pNaim;

                if (pDeveloper == 0) { sql.Parameters["@Developer"].Value = DBNull.Value; }
                else { sql.Parameters["@Developer"].Value = pDeveloper; }

                if (pHolder == 0) { sql.Parameters["@Holder"].Value = DBNull.Value; }
                else { sql.Parameters["@Holder"].Value = pHolder; }

                if (pSoftType == 0) { sql.Parameters["@SoftType"].Value = DBNull.Value; }
                else { sql.Parameters["@SoftType"].Value = pSoftType; }


                ColRowExecute = sql.ExecuteNonQuery();
                if (ColRowExecute != 1) { throw (new CommonException("Ошибка редактирования SoftType")); }

            }

            catch (Exception e) { throw e; }
            finally { }
        }


        public int AddSoftware(string pObozn, string pNaim, int pHolder, int pDeveloper,
                               int pSoftType)
        {

            SqlTransaction Trans = null;


            try
            {
                SqlCommand sql;
                int Result;

                Trans = mvarConnect.BeginTransaction("SampleTransaction");

                sql = new SqlCommand();
                sql.Transaction = Trans;
                sql.Connection = mvarConnect;

                sql.CommandText = " INSERT INTO DirSoftware"
                                + "(Obozn, Naim, Holder, Developer, SoftType)"
                                + " VALUES (@Obozn, @Naim, @Holder, @Developer, @SoftType);"
                                + " SELECT CAST(scope_identity() AS int)";

                sql.Parameters.Add("@Obozn", SqlDbType.NVarChar);
                sql.Parameters.Add("@Naim", SqlDbType.NVarChar);
                sql.Parameters.Add("@Holder", SqlDbType.Int);
                sql.Parameters.Add("@Developer", SqlDbType.Int);
                sql.Parameters.Add("@SoftType", SqlDbType.Int);



                sql.Parameters["@Obozn"].Value = pObozn;
                sql.Parameters["@Naim"].Value = pNaim;

                if (pDeveloper == 0) { sql.Parameters["@Developer"].Value = DBNull.Value; }
                else { sql.Parameters["@Developer"].Value = pDeveloper; }

                if (pHolder == 0) { sql.Parameters["@Holder"].Value = DBNull.Value; }
                else { sql.Parameters["@Holder"].Value = pHolder; }

                if (pSoftType == 0) { sql.Parameters["@SoftType"].Value = DBNull.Value; }
                else { sql.Parameters["@SoftType"].Value = pSoftType; }



                Result = (int)sql.ExecuteScalar();

                //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                //Как проверитьчто запись добавлена - критерий????


                Trans.Commit();

                return Result;

            }

            catch (Exception e)
            {
                if (Trans != null) { Trans.Rollback(); }
                throw e;
            }
            finally { }
        }

        # endregion



        # region {DirSoftType}

        public bool CheckRelationSoftType(int pId)
        {
            try
            {
                bool Result;

                if (CheckRelatSoftTypeSoft(pId) == false)
                { Result = false; }
                else { Result = true; }

                return Result;
            }

            catch (Exception e) { throw e; }
            finally { }
        }

        //Проверка связи Типа ПО с ПО
        private bool CheckRelatSoftTypeSoft(int pId)
        {
            try
            {

                SqlCommand sql;
                SqlDataReader rst;
                bool Result;

                sql = new SqlCommand();
                sql.Connection = mvarConnect;
                sql.CommandText = " SELECT DirSoftware.*"
                                + " FROM DirSoftware"
                                + " WHERE SoftType = " + pId;

                rst = sql.ExecuteReader();

                if (rst.HasRows == true) { Result = true; }
                else { Result = false; }

                rst.Close();


                return Result;

            }

            catch (Exception e) { throw e; }
            finally { }
        }

        public bool GetSoftType(int Id, out string Obozn, out string Naim)
        {
            try
            {
                SqlCommand sql;
                DataTable rst;
                SqlDataAdapter adp;
                DataRow row;
                bool Result;
                string lObozn;
                string lNaim;



                sql = new SqlCommand();
                sql.Connection = mvarConnect;

                sql.CommandText = " SELECT DirSoftType.*"
                                + " FROM DirSoftType"
                                + " WHERE Id = " + Id;

                adp = new SqlDataAdapter();
                adp.SelectCommand = sql;
                rst = new DataTable();
                adp.Fill(rst);

                if (rst.Rows.Count == 1)
                {
                    row = rst.Rows[0];

                    lObozn = (string)row["Obozn"];


                    if (Convert.IsDBNull(row["Naim"]) == true)
                    { lNaim = ""; }
                    else
                    { lNaim = (string)row["Naim"]; }


                    Result = true;
                }
                else
                {
                    lObozn = "";
                    lNaim = "";
                    Result = false;
                }


                Obozn = lObozn;
                Naim = lNaim;
                return Result;
            }

            catch (Exception e) { throw e; }
            finally { }
        }

        public int AddSoftType(string pObozn, string pNaim)
        {

            SqlTransaction Trans = null;

            try
            {
                SqlCommand sql;
                //int ColRowExecute;
                int Result;

                Trans = mvarConnect.BeginTransaction("SampleTransaction");

                sql = new SqlCommand();
                sql.Transaction = Trans;
                sql.Connection = mvarConnect;

                sql.CommandText = " INSERT INTO DirSoftType"
                                + "(Obozn, Naim)"
                                + " VALUES (@Obozn, @Naim);"
                                + " SELECT CAST(scope_identity() AS int)";

                sql.Parameters.Add("@Obozn", SqlDbType.NVarChar);
                sql.Parameters.Add("@Naim", SqlDbType.NVarChar);
                sql.Parameters["@Obozn"].Value = pObozn;
                sql.Parameters["@Naim"].Value = pNaim;

                //ColRowExecute = sql.ExecuteNonQuery();
                Result = (int)sql.ExecuteScalar();

                //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                //Как проверитьчто запись добавлена - критерий????

                //if (ColRowExecute != 1) { throw (new CommonException("Ошибка редактирования SoftType")); }


                Trans.Commit();

                return Result;
            }

            catch (Exception e)
            {
                if (Trans != null) { Trans.Rollback(); }
                throw e;
            }
            finally { }
        }

        public void DeleteSoftType(int pId)
        {
            try
            {
                SqlCommand sql;
                int ColRowExecute;

                sql = new SqlCommand();
                sql.Connection = mvarConnect;
                sql.CommandText = " DELETE FROM DirSoftType"
                                + " WHERE Id = " + pId;

                ColRowExecute = sql.ExecuteNonQuery();
                if (ColRowExecute != 1) { throw (new CommonException("Ошибка удаления SoftType")); }

            }

            catch (Exception e) { throw e; }
            finally { }
        }

        //Возвращает справочник типов софта
        public DataTable GetListSoftType()
        {
            try
            {
                SqlCommand sql;
                DataTable rst;
                SqlDataAdapter adp;

                sql = new SqlCommand();
                sql.Connection = mvarConnect;
                sql.CommandText = " SELECT DirSoftType.*"
                                + " FROM DirSoftType";

                adp = new SqlDataAdapter();
                adp.SelectCommand = sql;

                rst = new DataTable();
                adp.Fill(rst);

                return rst;

            }
            catch (Exception e) { throw e; }
            finally { }

        }

        public void EditSoftType(int pId, string pObozn, string pNaim)
        {
            try
            {
                SqlCommand sql;
                int ColRowExecute;

                sql = new SqlCommand();
                sql.Connection = mvarConnect;


                //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                //На время первоночального заполнения справочника
                ////                sql.CommandText = " UPDATE DirSoftType"
                ////                                + " SET  Naim = @Naim"
                ////                                + " WHERE Id = " + pId;

                sql.CommandText = " UPDATE DirSoftType"
                                + " SET  Naim = @Naim, Obozn = @Obozn"
                                + " WHERE Id = " + pId;
                sql.Parameters.Add("@Obozn", SqlDbType.NVarChar);
                sql.Parameters["@Obozn"].Value = pObozn;




                sql.Parameters.Add("@Naim", SqlDbType.NVarChar);
                sql.Parameters["@Naim"].Value = pNaim;
                ColRowExecute = sql.ExecuteNonQuery();
                if (ColRowExecute != 1) { throw (new CommonException("Ошибка редактирования ITCompany")); }

            }

            catch (Exception e) { throw e; }
            finally { }
        }



        # endregion



























































        ////        public void DeleteSoftware(int pId)
////        {
////            try
////            {
////                SqlCommand sql;
////                int ColRowExecute;
////                sql = new SqlCommand();
////                sql.Connection = mvarConnect;
////                sql.CommandText = " DELETE FROM DirSoftware"
////                                + " WHERE Id = " + pId;
////                ColRowExecute = sql.ExecuteNonQuery();
////                if (ColRowExecute != 1) { throw (new CommonException("Ошибка удаления ITCompany")); }
////
////            }
////            catch (Exception e) { throw e; }
////            finally { }
////        }


        public void DeleteSoftware(int pId)
        {
            try
            {
                SqlCommand sql;
                int ColRowExecute;

                mvarTransaction = mvarConnect.BeginTransaction("SampleTransaction");

                //Удаляем запись
                sql = new SqlCommand();
                sql.Transaction = mvarTransaction;
                sql.Connection = mvarConnect;
                sql.CommandText = " DELETE FROM DirSoftware"
                                + " WHERE Id = " + pId;
                ColRowExecute = sql.ExecuteNonQuery();
                if (ColRowExecute != 1) { throw (new CommonException("Ошибка удаления ITCompany")); }

                //Возвращаем максимальное значение Id  у котрого поле икримент
                int lMaxId;
                lMaxId = GetLastIdSoftware();

                //Сбрасываем до максимального значения
                ResetFieldIncrement("DirSoftware", lMaxId);

                mvarTransaction.Commit();
            }
            catch (Exception e)
            {
                if (mvarTransaction != null) { mvarTransaction.Rollback(); }
                throw e;
            }
            finally { mvarTransaction = null; }
        }




        //Сбрасывает поле инкримента в указанной табоице, до указанного значения
        private void ResetFieldIncrement(string pTable, int pNewIncrement)
        {
            try
            {

                SqlCommand sql;
                //SqlDataReader rst;

                sql = new SqlCommand();
                sql.Transaction = mvarTransaction;
                sql.Connection = mvarConnect;

                //sql.CommandText = "DBCC CHECKIDENT(@Table, NORESEED)";
                sql.CommandText = "DBCC CHECKIDENT(@Table, RESEED, @NewIncrement)";

                sql.Parameters.Add("@Table", SqlDbType.NVarChar);
                sql.Parameters["@Table"].Direction = ParameterDirection.Input;
                //sql.Parameters["@Table"].Value = "MrpData.dbo.DirSoftware";
                sql.Parameters["@Table"].Value = pTable;
                sql.Parameters.Add("@NewIncrement", SqlDbType.Int);
                sql.Parameters["@NewIncrement"].Direction = ParameterDirection.Input;
                sql.Parameters["@NewIncrement"].Value = pNewIncrement;

                ////sql.Parameters.Add("@CurId", SqlDbType.Int);
                ////sql.Parameters["@CurId"].Direction = ParameterDirection.ReturnValue;
                
                sql.ExecuteNonQuery();

                ////int ffff = (int)sql.Parameters["@CurId"].Value;

            }
            catch (Exception e1) { throw e1; }
            finally { }
        }



        //Вернуть последний код ИД Software
        public int GetLastIdSoftware()
        {
            try
            {
               SqlCommand sql;
               SqlDataReader rst;
               int lLastIdSoftware;

               sql = new SqlCommand();
               sql.Transaction = mvarTransaction;
               sql.Connection = mvarConnect;

               sql.CommandText = " SELECT MAX(DISTINCT Id) AS LastId"
                               + " FROM DirSoftware";
               rst = sql.ExecuteReader();
                
               if (rst.HasRows == true)
               {
                   rst.Read();
                   lLastIdSoftware = (int)rst["LastId"];
               }
               else { throw (new CommonException("Нет Id Software")); }
                
               rst.Close();

               return lLastIdSoftware;
            }
            catch (Exception e) { throw e; }
            finally { }
        }



        public void EditSoftware(int pId, string pObozn, string pNaim, int pDeveloper, int pHolder)
        {
            try
            {
                SqlCommand sql;
                int ColRowExecute;


                sql = new SqlCommand();
                sql.Connection = mvarConnect;





                //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                //На время первоночального заполнения справочника
////                sql.CommandText = " UPDATE DirSoftware"
////                                + " SET  Naim = @Naim, Holder = @Holder, Developer = @Developer"
////                                + " WHERE Id = " + pId;
                sql.CommandText = " UPDATE DirSoftware"
                                + " SET  Naim = @Naim, Holder = @Holder, Developer = @Developer, Obozn = @Obozn"
                                + " WHERE Id = " + pId;
                sql.Parameters.Add("@Obozn", SqlDbType.NVarChar);
                sql.Parameters["@Obozn"].Value = pObozn;




                sql.Parameters.Add("@Naim", SqlDbType.NVarChar);
                sql.Parameters.Add("@Developer", SqlDbType.Int);
                sql.Parameters.Add("@Holder", SqlDbType.Int);


                sql.Parameters["@Naim"].Value = pNaim;

                if (pDeveloper == 0) { sql.Parameters["@Developer"].Value = DBNull.Value; }
                else { sql.Parameters["@Developer"].Value = pDeveloper; }

                if (pHolder == 0) { sql.Parameters["@Holder"].Value = DBNull.Value; }
                else { sql.Parameters["@Holder"].Value = pHolder; }


                ColRowExecute = sql.ExecuteNonQuery();
                if (ColRowExecute != 1) { throw (new CommonException("Ошибка редактирования Software")); }

            }

            catch (Exception e) { throw e; }
            finally { }
        }


        public void EditITCompany(int pId, string pObozn, string pNaim)
        {
            try
            {
                SqlCommand sql;
                int ColRowExecute;


                sql = new SqlCommand();
                sql.Connection = mvarConnect;







//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
//На время первоночального заполнения справочника
////                sql.CommandText = " UPDATE DirITCompany"
////                                + " SET  Naim = @Naim"
////                                + " WHERE Id = " + pId;

                sql.CommandText = " UPDATE DirITCompany"
                                + " SET  Naim = @Naim, Obozn = @Obozn"
                                + " WHERE Id = " + pId;
                sql.Parameters.Add("@Obozn", SqlDbType.NVarChar);
                sql.Parameters["@Obozn"].Value = pObozn;











                sql.Parameters.Add("@Naim", SqlDbType.NVarChar);
                sql.Parameters["@Naim"].Value = pNaim;
                ColRowExecute = sql.ExecuteNonQuery();
                if (ColRowExecute != 1) { throw (new CommonException("Ошибка редактирования ITCompany")); }

            }

            catch (Exception e) { throw e; }
            finally { }
        }






























        //Возвращает справочник компаний
        public DataTable GetListSoftware()
        {
            try
            {
                SqlCommand sql;
                DataTable rst;
                SqlDataAdapter adp;

                sql = new SqlCommand();
                sql.Connection = mvarConnect;
                sql.CommandText = " SELECT DirSoftware.*"
                                + " FROM DirSoftware";

                adp = new SqlDataAdapter();
                adp.SelectCommand = sql;

                rst = new DataTable();
                adp.Fill(rst);

                return rst;

            }
            catch (Exception e) { throw e; }
            finally { }

        }






























































        #region {ITCompany}

        //Возвращает справочник компаний
        public DataTable GetListITCompany()
        {
            try
            {
                SqlCommand sql;
                DataTable rst;
                SqlDataAdapter adp;

                sql = new SqlCommand();
                sql.Connection = mvarConnect;
                sql.CommandText = " SELECT DirITCompany.*"
                                + " FROM DirITCompany";

                adp = new SqlDataAdapter();
                adp.SelectCommand = sql;

                rst = new DataTable();
                adp.Fill(rst);

                return rst;
       
            }
            catch (Exception e) { throw e; }
            finally { }

        }



        public bool CheckRelationITCompany(int pId)
        {
            try
            {
                bool Result;

                if (CheckRelatITCompSoftDev(pId) == false & CheckRelatITComSoftHold(pId) == false)
                { Result = false; }
                else { Result = true; }

                return Result;
            }

            catch (Exception e) { throw e; }
            finally { }
        }

        //Проверка связи ИТ компании с разработчиком софта
        private bool CheckRelatITCompSoftDev(int pId)
        {
            try
            {

                SqlCommand sql;
                SqlDataReader rst;
                bool Result;

                sql = new SqlCommand();
                sql.Connection = mvarConnect;
                sql.CommandText = " SELECT DirSoftware.*"
                                + " FROM DirSoftware"
                                + " WHERE Developer = " + pId;

                rst = sql.ExecuteReader();

                if (rst.HasRows == true) { Result = true; }
                else { Result = false; }

                rst.Close();


                return Result;

            }

            catch (Exception e) { throw e; }
            finally { }
        }

        //Проверка связи ИТ компании с владельцем софта
        private bool CheckRelatITComSoftHold(int pId)
        {
            try
            {

                SqlCommand sql;
                SqlDataReader rst;
                bool Result;

                sql = new SqlCommand();
                sql.Connection = mvarConnect;
                sql.CommandText = " SELECT DirSoftware.*"
                                + " FROM DirSoftware"
                                + " WHERE Holder = " + pId;

                rst = sql.ExecuteReader();

                if (rst.HasRows == true) { Result = true; }
                else { Result = false; }

                rst.Close();

                return Result;

            }

            catch (Exception e) { throw e; }
            finally { }
        }

        public void DeleteITCompany(int pId)
        {
            try
            {
                SqlCommand sql;
                int ColRowExecute;

                sql = new SqlCommand();
                sql.Connection = mvarConnect;
                sql.CommandText = " DELETE FROM DirITCompany"
                                + " WHERE Id = " + pId;

                ColRowExecute = sql.ExecuteNonQuery();
                if (ColRowExecute != 1) { throw (new CommonException("Ошибка удаления ITCompany")); }

            }

            catch (Exception e) { throw e; }
            finally { }
        }

        public int AddITCompany(string pObozn, string pNaim)
        {

            SqlTransaction Trans = null;


            try
            {
                SqlCommand sql;
                //int ColRowExecute;
                int Result;

                Trans = mvarConnect.BeginTransaction("SampleTransaction");

                sql = new SqlCommand();
                sql.Transaction = Trans;
                sql.Connection = mvarConnect;

                sql.CommandText = " INSERT INTO DirITCompany"
                                + "(Obozn, Naim)"
                                + " VALUES (@Obozn, @Naim);"
                                + " SELECT CAST(scope_identity() AS int)";

                sql.Parameters.Add("@Obozn", SqlDbType.NVarChar);
                sql.Parameters.Add("@Naim", SqlDbType.NVarChar);
                sql.Parameters["@Obozn"].Value = pObozn;
                sql.Parameters["@Naim"].Value = pNaim;

                ////ColRowExecute = sql.ExecuteNonQuery();
                Result = (int)sql.ExecuteScalar();

                //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                //Как проверитьчто запись добавлена - критерий????

                ////if (ColRowExecute != 1) { throw (new CommonException("Ошибка редактирования ITCompany")); }


                Trans.Commit();

                return Result;

            }

            catch (Exception e)
            {
                if (Trans != null) { Trans.Rollback(); }
                throw e;
            }
            finally { }
        }

 




        public bool GetITCompany(int Id, out string Obozn, out string Naim)
        {
            try
            {
                SqlCommand sql;
                DataTable rst;
                SqlDataAdapter adp;
                DataRow row;
                bool Result;
                string lObozn;
                string lNaim;



                sql = new SqlCommand();
                sql.Connection = mvarConnect;

                sql.CommandText = " SELECT DirITCompany.*"
                                + " FROM DirITCompany"
                                + " WHERE Id = " + Id;

                adp = new SqlDataAdapter();
                adp.SelectCommand = sql;
                rst = new DataTable();
                adp.Fill(rst);

                if (rst.Rows.Count == 1)
                {
                    row = rst.Rows[0];

                    lObozn = (string)row["Obozn"];


                    if (Convert.IsDBNull(row["Naim"]) == true)
                    { lNaim = ""; }
                    else
                    { lNaim = (string)row["Naim"]; }


                    Result = true;
                }
                else
                {
                    lObozn = "";
                    lNaim = "";
                    Result = false;
                }


                Obozn = lObozn;
                Naim = lNaim;
                return Result;
            }

            catch (Exception e) { throw e; }
            finally { }
        }

        #endregion






        #region {Software}

 

        public int AddSoftware(string pObozn, string pNaim,int pHolder, int pDeveloper)
        {

            SqlTransaction Trans = null;


            try
            {
                SqlCommand sql;
                int Result;

                Trans = mvarConnect.BeginTransaction("SampleTransaction");

                sql = new SqlCommand();
                sql.Transaction = Trans;
                sql.Connection = mvarConnect;

                sql.CommandText = " INSERT INTO DirSoftware"
                                + "(Obozn, Naim, Holder, Developer)"
                                + " VALUES (@Obozn, @Naim, @Holder, @Developer);"
                                + " SELECT CAST(scope_identity() AS int)";

                sql.Parameters.Add("@Obozn", SqlDbType.NVarChar);
                sql.Parameters.Add("@Naim", SqlDbType.NVarChar);
                sql.Parameters.Add("@Holder", SqlDbType.Int);
                sql.Parameters.Add("@Developer", SqlDbType.Int);


                sql.Parameters["@Obozn"].Value = pObozn;
                sql.Parameters["@Naim"].Value = pNaim;

                if (pDeveloper == 0) { sql.Parameters["@Developer"].Value = DBNull.Value; }
                else { sql.Parameters["@Developer"].Value = pDeveloper; }

                if (pHolder == 0) { sql.Parameters["@Holder"].Value = DBNull.Value; }
                else { sql.Parameters["@Holder"].Value = pHolder; }


                Result = (int)sql.ExecuteScalar();

                //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                //Как проверитьчто запись добавлена - критерий????


                Trans.Commit();

                return Result;

            }

            catch (Exception e)
            {
                if (Trans != null) { Trans.Rollback(); }
                throw e;
            }
            finally { }
        }



        public bool GetSoftware(int Id, out string Obozn, out string Naim,
                                 out int Holder, out int Developer)
        {
            try
            {
                SqlCommand sql;
                DataTable rst;
                SqlDataAdapter adp;
                DataRow row;
                bool Result;
                string lObozn;
                string lNaim;
                int lHolder;
                int lDeveloper;


                sql = new SqlCommand();
                sql.Connection = mvarConnect;

                sql.CommandText = " SELECT DirSoftware.*"
                                + " FROM DirSoftware"
                                + " WHERE Id = " + Id;

                adp = new SqlDataAdapter();
                adp.SelectCommand = sql;
                rst = new DataTable();
                adp.Fill(rst);

                if (rst.Rows.Count == 1)
                {
                    row = rst.Rows[0];

                    lObozn = (string)row["Obozn"];

                    if (Convert.IsDBNull(row["Naim"]) == true) { lNaim = ""; }
                    else { lNaim = (string)row["Naim"]; }

                    if (Convert.IsDBNull(row["Holder"]) == true) { lHolder = 0; }
                    else { lHolder = (int)row["Holder"]; }

                    if (Convert.IsDBNull(row["Developer"]) == true) { lDeveloper = 0; }
                    else { lDeveloper = (int)row["Developer"]; }

                    Result = true;
                }
                else
                {
                    lObozn = "";
                    lNaim = "";
                    lHolder = 0;
                    lDeveloper = 0;

                    Result = false;
                }


                Obozn = lObozn;
                Naim = lNaim;
                Holder = lHolder;
                Developer = lDeveloper;
                return Result;
            }

            catch (Exception e) { throw e; }
            finally { }
        }
        #endregion














        protected TSysMrpZzva(SqlConnection pConnect)
        {
            try { mvarConnect = pConnect; }
            catch (Exception e) { throw e; }
            finally { }
        }

        public static TSysMrpZzva GetSystem()
        {
                try
                {
                    if (mvarSysMrpZzva == null){mvarSysMrpZzva = GetSysMrpZzva();}
                 
                    return mvarSysMrpZzva;
                }

                catch (Exception e) { throw e; }
                finally { }
        }

        private static TSysMrpZzva GetSysMrpZzva()
        {
            string lConnectStr;
            SqlConnection lConnect;
            TSysMrpZzva lSysMrpZzva;

            try
            {
                //!!!!!!!!!!!!!
                //достаем Connect из установок
                ////Connect = RegisterLokal.GetSetting("Common", "SysBuhuchConnect");
                //lConnectStr = "Data Source=TEH-SRV;Initial Catalog=MrpData;Integrated Security=True;";
                //lConnectStr = lConnectStr + "User ID=" + User + ";Password=" + Password + ";";
                lConnectStr = "Data Source=TEH-SRV;Initial Catalog=MrpData;Integrated Security=True;"
                            + "User ID=Sergey;Password=Cl254J;";
                lConnect = new SqlConnection(lConnectStr);
                lConnect.Open();
                lSysMrpZzva = new TSysMrpZzva(lConnect);
                return lSysMrpZzva;
            }
            catch (Exception e) { throw e; }
            finally { }
        }





 















 



        public string  TestConnect(){return "YesConnectMrpZzva";}






    }
}
